package com.voicenews.dao;

import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.voicenews.model.Article;

public class ArticleDAO {
	public static String INSERT_ARTICLE = "INSERT INTO article"
			+ "(title, description, url, image_link, pubdate, feed_id, content) VALUES"
			+ "(?,?,?,?,?,?,?)";
	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet resultSet = null;

	public ArticleDAO() {
		// TODO Auto-generated constructor stub
	}

	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public long add(Article article) {
		// TODO Auto-generated method stub
		ResultSet generatedKeys = null;
		long id = -1;
		try {
			connection = this.getConnection();
			ptmt = connection.prepareStatement(INSERT_ARTICLE,
					Statement.RETURN_GENERATED_KEYS);
			ptmt.setString(1, article.getTitle());
			ptmt.setString(2, article.getDescription());
			ptmt.setString(3, article.getUrl().toString());
			ptmt.setString(4, article.getImgLink());
			ptmt.setLong(5, new Date(article.getPubDate()).getTime());
			ptmt.setLong(6, article.getFeedId());
			ptmt.setString(7, article.getContent());

			int affectedRows = ptmt.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Creating user failed, no rows affected.");
			}

			generatedKeys = ptmt.getGeneratedKeys();
			if (generatedKeys.next()) {
				id = generatedKeys.getLong(1);
				article.setArticleId(id);
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}

			System.out.println("id : " + article.getArticleId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (ptmt != null)
				try {
					ptmt.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

		return id;
	}

	public List<Article> getArticlesByTopic(String topic, int offset) {
		List<Article> articles = new ArrayList<Article>();
		String query = "select * from article where feed_id in (select id from feed where topic_id in (select id from topic where topic.name like '"
				+ topic + "')) order by pubdate desc limit 10 offset " + offset;
		System.out.println(query);
		try {
			connection = getConnection();
			ptmt = connection.prepareStatement(query);
			// prepared.setString(1, topic);
			// prepared.setLong(2, offset);
			ResultSet result = ptmt.executeQuery(query);
			while (result.next()) {
				Article article = new Article();
				article.setArticleId(result.getLong("id"));
				article.setTitle(result.getString("title"));
				article.setDescription(result.getString("description"));
				article.setUrl(new java.net.URL(result.getString("url")));
				article.setImgLink(result.getString("image_link"));
				article.setPubDate(String.valueOf(result.getLong("pubdate")));
				article.setFeedId(result.getLong("feed_id"));
				String title = result.getString("title");
				String date = new Date(result.getLong("pubdate")).toString();
				System.out.println(title + "\t" + date);
				articles.add(article);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (MalformedURLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ptmt != null)
				try {
					ptmt.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return articles;
	}

	public String getContentOfArticle(String url) {
		String queString = "select content from article where url = '" + url
				+ "'";
		ResultSet result = null;
		String content = null;

		try {
			connection = this.getConnection();
			ptmt = connection.prepareStatement(queString);

			result = ptmt.executeQuery(queString);
			if (result.next()) {
				content = result.getString("content");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ptmt != null)
				try {
					ptmt.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return content;
	}

	public void addArticleToUser(String idUser, String urlString) {
		// TODO Auto-generated method stub
		String queString = "select id from article where url = '" + urlString
				+ "'";
		String INSERT_USER_ARTICLE = "INSERT INTO user_article"
				+ "(user_id, article_id) VALUES" + "(?,?)";
		Statement statement = null;
		java.sql.PreparedStatement st = null;
		ResultSet result = null;
		String idArticle = null;
		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(queString);

			result = statement.executeQuery(queString);
			if (result.next()) {
				idArticle = String.valueOf(result.getLong("id"));
				st = connection.prepareStatement(INSERT_USER_ARTICLE);
				st.setString(1, idUser);
				st.setString(2, idArticle);
				st.executeUpdate();
			} else {
				return;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException ex) {

				}
			}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

}
